package chen.web.message;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import chen.web.DataSourceProvider;
import chen.web.MyContextProvider;
import chen.web.user.Account;
import chen.web.user.AccountManager;
import chen.web.util.DateInt;

public class MessageManagerImpl implements MessageManager {
	
	private SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());
	
	private static final String SQL_8 = "select msg_delete from web_message where msg_id = ?";
	private static final String SQL_7 = "update web_message set msg_delete = ? where msg_id = ?";
	private static final String SQL_9 = "delete from web_message where msg_id = ?";
	
	private AccountManager am = null;
	public MessageManagerImpl(AccountManager am){
		this.am = am;
	}
	
	@Override
	public void delete(Message message) {
		// TODO Auto-generated method stub
		String delete = null;
		try{
			delete = simpleJdbcTemplate.queryForObject(SQL_8, String.class, message.getId());
		} catch (EmptyResultDataAccessException e) {
			// TODO Auto-generated catch block
			return;
		}
		
		Account cua = MyContextProvider.getMyContext().getAccount();
		
		if(message.getReceiver().equals(cua)){	//接收者删除
			if("SenderDeleted".equals(delete)) 
				simpleJdbcTemplate.update(SQL_9, message.getId());
			  else if("NotDeleted".equals(delete)) 
				simpleJdbcTemplate.update(SQL_7, "ReceiverDeleted", message.getId());
		}
		
		if(message.getSender().equals(cua)){	//发送者删除
			if("ReceiverDeleted".equals(delete))
				simpleJdbcTemplate.update(SQL_9, message.getId());
			else if("NotDeleted".equals(delete))
				simpleJdbcTemplate.update(SQL_7, "SenderDeleted", message.getId());
		}
	}

	private static final String SQL_6 = "select * from web_message where msg_id = ?";
	@Override
	public Message getMessage(int messageId) {
		// TODO Auto-generated method stub
		Message message = null;
		try {
			message = simpleJdbcTemplate.queryForObject(SQL_6, new MessageRowMapper(), messageId);
			Account sender = am.getAccount(message.getSender().getId());
			Account receiver = am.getAccount(message.getReceiver().getId());
			message.setSender(sender);
			message.setReceiver(receiver);
		} catch (EmptyResultDataAccessException e) {
			// TODO Auto-generated catch block
			//do nothing
		}
		return message;
	}

	private static final String SQL_5 = "select * from web_message where ((msg_senderId = ? and msg_receiverId = ?) or (msg_senderId = ? and msg_receiverId = ?)) and msg_sendTime < ? order by msg_sendTime desc limit ?, ?";
	@Override
	public List<Message> getRecord(Account acc1, Account acc2, Date before, int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Message>  list = simpleJdbcTemplate.query(SQL_5, new MessageRowMapper(), acc1.getId(), acc2.getId(), acc2.getId(), acc1.getId(), DateInt.transform(before), beginOffset, count);
		int id1 = acc1.getId();
		for(Message message : list){
			int receiverId = message.getReceiver().getId();
			if(receiverId == id1){
				message.setReceiver(acc1);
				message.setSender(acc2);
			} else{
				message.setReceiver(acc2);
				message.setSender(acc1);
			}
		}
		return list;
	}

	private static final String SQL_4 = "select count(*) from web_message where msg_receiverId = ? and msg_read = 0";
	@Override
	public int newMessageCount(Account account){
		int n = simpleJdbcTemplate.queryForInt(SQL_4, account.getId());
		return n;
	}
	

	private static final String SQL_3 = "select * from web_message where msg_receiverId = ? and msg_delete != 'ReceiverDeleted' order by msg_sendTime desc limit ?, ?";
	@Override
	public void fill(Inbox inbox) {
		// TODO Auto-generated method stub
		List<Message>  list = simpleJdbcTemplate.query(SQL_3, new MessageRowMapper(), inbox.getBelong().getId(), inbox.getBeginOffset(), inbox.getCapacity());
		for(Message message : list){
			message.setReceiver(inbox.getBelong());
			int senderId = message.getSender().getId();
			Account sender = am.getAccount(senderId);
			message.setSender(sender);
		}
		inbox.setMessages(list);
	}

	private static final String SQL_2 = "select * from web_message where msg_senderId = ? and msg_delete != 'SenderDeleted' order by msg_sendTime desc limit ?, ?";
	@Override
	public void fill(Outbox outbox) {
		// TODO Auto-generated method stub
		List<Message>  list = simpleJdbcTemplate.query(SQL_2, new MessageRowMapper(), outbox.getBelong().getId(), outbox.getBeginOffset(), outbox.getCapacity());
		for(Message message : list){
			message.setSender(outbox.getBelong());
			int receiverId = message.getReceiver().getId();
			Account receiver = am.getAccount(receiverId);
			message.setReceiver(receiver);
		}
		outbox.setMessages(list);
	}

	private static final String SQL_1 = "insert into web_message(msg_senderId, msg_receiverId, msg_content, msg_sendTime) values(?,?,?,?)";
	@Override
	public void send(Message message) {
		// TODO Auto-generated method stub
		if(message.getSendTime() == null){
			message.setSendTime(new Date());
		}
		if(message.getSender() == null){
			message.setSender(MyContextProvider.getMyContext().getAccount());
		}
		simpleJdbcTemplate.update(SQL_1, message.getSender().getId(), message.getReceiver().getId(), message.getContent(), DateInt.transform(message.getSendTime()));
	}

	private static final class MessageRowMapper implements RowMapper<Message>{
		@Override
		public Message mapRow(ResultSet rs, int n) throws SQLException {
			// TODO Auto-generated method stub
			Message message = new Message(rs.getInt("msg_id"));
			message.setContent(rs.getString("msg_content"));
			message.setFlag(rs.getInt("msg_flag"));
			message.setRead(rs.getBoolean("msg_read"));
			int reId = rs.getInt("msg_receiverId");
			Account receiver = new Account(reId);
			message.setReceiver(receiver);
			int senderId = rs.getInt("msg_senderId");
			Account sender = new Account(senderId);
			message.setSender(sender);
			message.setSendTime(DateInt.transform(rs.getInt("msg_sendTime")));
			return message;
		}
	}

	private static final String SQL_10 = "select count(*) from web_message where msg_receiverId = ? and msg_delete != 'ReceiverDeleted'";
	@Override
	public int totalReceived(Account acc) {
		// TODO Auto-generated method stub
		int n = simpleJdbcTemplate.queryForInt(SQL_10, acc.getId());
		return n;
	}

	private static final String SQL_11 = "select count(*) from web_message where msg_senderId = ? and msg_delete != 'SenderDeleted'";
	@Override
	public int totalSent(Account acc) {
		// TODO Auto-generated method stub
		int n = simpleJdbcTemplate.queryForInt(SQL_11, acc.getId());
		return n;
	}

	private static final String SQL_23 = "update web_message set msg_read = true where msg_id = ?";
	@Override
	public void hasRead(Message message) {
		// TODO Auto-generated method stub
		message.setRead(true);
		simpleJdbcTemplate.update(SQL_23, message.getId());
	}
	
	private static final String SQL_32 = "select * from web_message where msg_receiverId = ? and msg_read = 0 order by msg_id desc limit ?,?";
	@Override
	public List<Message> listNewMessage(Account account, int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Message> list = simpleJdbcTemplate.query(SQL_32, new MessageRowMapper(), account.getId(), beginOffset, count);
		for(Message message : list){
			message.setReceiver(account);
			int senderId = message.getSender().getId();
			Account sender = am.getAccount(senderId);
			message.setSender(sender);
		}
		return list;
	}

}
